﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class LoaiTBDAO
    {
        // LOADLOAITHIETBIDIEN
        public DataTable LoadLoaiTBDien()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADLOAITHIETBIDIEN", _cn);
            _da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

        //THEMLOAITHIETBIDIEN
        public void ThemLoaiTBDien(LoaiTBDTO ltbDTO)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMLOAITHIETBIDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            //cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));

            //cmd.Parameters["@MaTB"].Value = ltbDTO.MALOAI;
            cmd.Parameters["@TenLoai"].Value = ltbDTO.TENLOAI;

            //int _kq = cmd.ExecuteNonQuery();
            cmd.ExecuteNonQuery();
            _cn.Close();
            // return _kq;
        }

        //SUALOAITHIETBIDIEN
        public int SuaLoaiTBDien(LoaiTBDTO ltbDTO)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("SUALOAITHIETBIDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));

            cmd.Parameters["@MaTB"].Value = ltbDTO.MALOAI;
            cmd.Parameters["@TenLoai"].Value = ltbDTO.TENLOAI;

            int _kq = cmd.ExecuteNonQuery();
            //cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

        //XOALOAITHIETBIDIEN
        public int XoaLoaiTBDien(LoaiTBDTO ltbDTO)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("XOALOAITHIETBIDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            //cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));

            cmd.Parameters["@MaTB"].Value = ltbDTO.MALOAI;
            //cmd.Parameters["@TenLoai"].Value = ltbDTO.TENLOAI;

            int _kq = cmd.ExecuteNonQuery();
            //cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

        //KIEMTRALOAITHIETBIDIEN
        public int KTLoaiTBDien(LoaiTBDTO ltbDTO)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("KTLOAITBDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            //cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@out", SqlDbType.Int));
            cmd.Parameters["@out"].Direction = ParameterDirection.Output;

            //cmd.Parameters["@MaLoai"].Value = ltbDTO.MALOAI;
            cmd.Parameters["@TenLoai"].Value = ltbDTO.TENLOAI;

            cmd.ExecuteNonQuery();
            int _kq = (int)cmd.Parameters["@out"].Value;
            _cn.Close();
            return _kq;
        }

        //KIEMTRAMALOAITHIETBIDIEN
        public int KTMaLoaiTBDien(LoaiTBDTO ltbDTO)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("KTMALOAIKICHDIEN", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            //cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@out", SqlDbType.Int));
            cmd.Parameters["@out"].Direction = ParameterDirection.Output;

            cmd.Parameters["@MaLoai"].Value = ltbDTO.MALOAI;
            //cmd.Parameters["@TenLoai"].Value = ltbDTO.TENLOAI;

            cmd.ExecuteNonQuery();
            int _kq = (int)cmd.Parameters["@out"].Value;
            _cn.Close();
            return _kq;
        }
/*-------------------------LOAD----------------------------------------------------
LOADLOAITB
LOADLOAITB_MALOAI @MaLoai int
LOADLOAITB_TENLOAI @TenLoai nvarchar(20)*/

        public DataTable LoadLoaiTB()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADLOAITB", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }
        public DataTable LoadLoaiTB_MaLoai(int MaLoai)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADLOAITB_MALOAI", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@MaLoai", SqlDbType.Int).Value = MaLoai;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }
        public DataTable LoadLoaiTB_TenLoai(string TenLoai)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("LOADLOAITB_TENLOAI", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;
            _da.SelectCommand.Parameters.Add("@TenLoai", SqlDbType.NVarChar, 20).Value = TenLoai;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

/*-------------------------INSERT----------------------------------------------------
THEMLOAITB @TenLoai nvarchar(20)
 */
        //Trả về 1 nếu thành công, 0 ngược lại
        public int ThemLoaiTB(LoaiTBDTO ltb)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMLOAITB", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 20));
            cmd.Parameters["@TenLoai"].Value = ltb.TENLOAI;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
/*-------------------------UPDATE----------------------------------------------------
SUALOAITB @MaLoai int,@TenLoai nvarchar(20)
 */
        public int SuaLoaiTB(LoaiTBDTO ltb)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMLOAITB", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 20));
            cmd.Parameters["@MaLoai"].Value = ltb.MALOAI;
            cmd.Parameters["@TenLoai"].Value = ltb.TENLOAI;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
/*-------------------------DELETE----------------------------------------------------
XOALOAITB @MaLoai int
*/
        public int XoaLoaiTB(string MaLoai)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEMLOAITB", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            cmd.Parameters["@MaLoai"].Value = MaLoai;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }
    }
}
